
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.engine import reflection
from sqlalchemy import text
import os
from sqlalchemy.ext.declarative import declarative_base
from cuslog import CusLog
import pathlib
Base = declarative_base()


class DbUtil(object):
    def __init__(self, logger, config):
        self.logger = logger
        self.config = config

    def createdb(self):
        conn = None
        try:
            user = self.config.get("db", "user")
            password = self.config.get("db", "password")
            host = self.config.get("db", "host")
            port = self.config.get("db", "port")
            dbname = self.config.get("db", "dbname")
            engine = create_engine(
                f"postgresql://{user}:{password}@{host}:{port}/postgres",
                isolation_level="AUTOCOMMIT",
            )
            conn = engine.connect()
            query = text(
                "SELECT datname FROM pg_database WHERE datistemplate = false;")
            result = conn.execute(query).fetchall()
            database_names = [d[0] for d in result]
            if dbname in database_names:
                self.logger.log_info(f"Database {dbname} already exists.")
            else:
                try:
                    conn.execute(
                        text(f"CREATE DATABASE {dbname}  owner {user} ENCODING 'utf-8';"))
                    self.logger.log_info(
                        f"Database {dbname} created successfully with UTF-8 encoding."
                    )
                except Exception as e:
                    self.logger.log_info(f"Error creating database: {e}")
        except Exception as ex:
            self.logger.log_error(ex)
        finally:
            if conn is not None:
                conn.close()
    def excetsql_by_dir(self,dir,ignore_init):
    # PostgreSQL数据库连接信息
        try:
            user = self.config.get("db", "user")
            password = self.config.get("db", "password")
            host = self.config.get("db", "host")
            port = self.config.get("db", "port")
            dbname = self.config.get("db", "dbname")
            engine = create_engine(
                f"postgresql://{user}:{password}@{host}:{port}/{dbname}",
                # isolation_level="AUTOCOMMIT",
            )
            conn = engine.connect()
            for f in os.listdir(path= dir):
                if pathlib.Path(f)=='.sql':
                    self.logger.log_info("find sql file:"+f)
                    initfile = f.startswith("00")
                    


            
            logging.info("find sql file:"+file)
            print("find sql file: "+file)

            if (type == 0 or (type == 1 and initfile == False)):
                logging.info("excete sql :"+file)
                print("excete sql :"+file)
                with open(os.path.join(exe_dir, 'sql', file), 'r', encoding="utf-8") as f:
                    sql_script = f.read()
                    # 执行SQL脚本
                    with conn.cursor() as cursor:
                        cursor.execute(sql_script)
                        conn.commit()
                        print(f"{file} executed.")
                        logging.info(f"{file} executed.")
                f.close()
        except Exception as e:
            logging.error(e)
            print(e)
        finally:
            # 释放数据库连接
            try:
                if conn is not None:
                    conn.close()
                    logging.info("connect is closed")
                    print("connect is closed")
            except psycopg2.Error as e:
                logging.info("connect  close error:", e)
                print("connect  close error:", e)